/**  定义Exercises相关的接口 */
const express = require("express");
const router = express.Router();
const Response = require("../utils/Response.js");
// 引入mysql连接池
const pool = require("../utils/db.js");

// 添加成绩
router.post("/grades/add", (req, resp) => {
  let obj = req.body
  let sql = "insert into grades set ?"
  pool.query(sql, [obj], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok());
  })
});

// 查询用户科目成绩单
router.get("/grades/user", (req, resp) => {
  let obj = req.query
  let sql = 'select * from grades where userId=? and grade_subj=?'
  pool.query(sql, [obj.userId,obj.grade_subj], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok(result));
  })
});

// // 查询用户成绩单 (按日期排)
// router.get("/grades/user", (req, resp) => {
//   let obj = req.query
//   let sql = 'select * from grades where userId=?'
//   pool.query(sql, [obj.userId], (error, result) => {
//     if (error) {
//       resp.send(Response.error(500, error));
//       throw error;
//     }
//     resp.send(Response.ok(result));
//   })
// });

// 查询用户成绩单 (按日期降序排)
router.get("/grades/user/1", (req, resp) => {
  let obj = req.query
  let sql = 'select * from grades order by grade_id desc where userId=? and grade_subj=?'
  pool.query(sql, [obj.userId], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok(result));
  })
});

// 查询用户成绩单 (按分数排)
router.get("/grades/user/2", (req, resp) => {
  let obj = req.query
  let sql = 'select * from grades order by grade_num desc where userId=? and grade_subj=?'
  pool.query(sql, [obj.userId], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok(result));
  })
});

// 查询所有用户成绩排名
router.get("/grades/ranking", (req, resp) => {
  let sql = 'select grade_num,grade_subj,userName from grades order by grade_num desc'
  // let sql = 'select grade_num,user_name from grades,user_b order by grade_num desc where userId=user_id'
  pool.query(sql, [], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok(result));
  })
});


module.exports = router;